package com.light.util;

import com.light.exception.H2Exception;
import com.light.model.GitAuthInfo;
import com.light.model.GitNotice;
import com.light.model.GitProject;
import javafx.beans.property.SimpleBooleanProperty;
import javafx.beans.property.SimpleDoubleProperty;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import org.apache.commons.lang3.StringUtils;
import org.h2.jdbcx.JdbcConnectionPool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * @Author: wangss
 * @CreateTime: 2023-05-19  22:36
 * @Description: H2连接池
 */
public class H2PoolUtils {

    public static final Logger LOGGER = LoggerFactory.getLogger(H2PoolUtils.class);
    private static JdbcConnectionPool connectionPool = null;
    private static final String DB_URL;
    private static final String DB_URL_PREFIX;
    private static final String DB_URL_SUFFIX;
    private static final String DB_USERNAME;
    private static final String DB_PASSWORD;
    private static final int DB_POOL_MAXACTIVE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(H2PoolUtils.class.getResourceAsStream("/db.properties"));
            DB_URL_PREFIX = properties.getProperty("jdbc.url.prefix", "jdbc:h2:file:");
            DB_URL_SUFFIX = properties.getProperty("jdbc.url.suffix", "/.h2/git-db;AUTO_SERVER=TRUE");
            DB_USERNAME = properties.getProperty("jdbc.username", "git");
            DB_PASSWORD = properties.getProperty("jdbc.password", "git@123");
            DB_POOL_MAXACTIVE = Integer.parseInt(properties.getProperty("jdbc.pool.maxActive", "20"));
            DB_URL = DB_URL_PREFIX + System.getProperty("user.home") + DB_URL_SUFFIX;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取链接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        if (connectionPool == null) {
            createConnectionPool();
        }
        return connectionPool.getConnection();
    }

    /**
     * 创建连接池
     */
    private static void createConnectionPool() {
        connectionPool = JdbcConnectionPool.create(DB_URL, DB_USERNAME, DB_PASSWORD);
        connectionPool.setMaxConnections(DB_POOL_MAXACTIVE);
        LOGGER.info("h2 连接池初始化成功");
    }

    /**
     * 关闭连接池
     */
    public static void closeConnectionPool() {
        if (null != connectionPool) {
            connectionPool.dispose();
            LOGGER.info("h2 连接池关闭");
        }
    }

    /**
     * 创建项目所需表
     *
     * @throws SQLException
     */
    public static void createTable() throws SQLException {
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("drop table git_project_info if exists");
        stmt.executeUpdate("drop table git_project_dict if exists");
        stmt.executeUpdate("drop table git_project_notice_history if exists");
        stmt.executeUpdate("drop table git_auth_info if exists");
        // 初始化项目信息表
        String createGitProjectInfoTable = """
                create table git_project_info
                (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    name VARCHAR(100) NOT NULL comment '项目名称',
                    author varchar(100) comment '作者',
                    branch VARCHAR(100) comment '当前分支',
                    remote varchar(255) comment '仓库地址',
                    local varchar(255) comment '本地地址',
                    createtime varchar(20) comment '创建时间',
                    updatetime varchar(20) comment '更新时间',
                    description VARCHAR(1000) comment '描述',
                    remark VARCHAR(1000) comment '备注',
                    level int default 0 comment '学习等级'
                )
                """;
        stmt.executeUpdate(createGitProjectInfoTable);
        LOGGER.info("h2 项目信息表创建成功");

        // 初始化字典表
        String createGitProjectDictTable = """
                create table git_project_dict
                (
                  id int primary key auto_increment,
                  label varchar(100) comment '标签',
                  label_value varchar(100) comment '值',
                  description VARCHAR(255) comment '描述',
                  createtime varchar(20) comment '创建时间',
                  updatetime varchar(20) comment '更新时间'
                )
                """;
        stmt.executeUpdate(createGitProjectDictTable);
        LOGGER.info("h2 字典表创建成功");

        // 初始化通知历史表
        String createGitProjectNoticeHistoryTable = """
                create table git_project_notice_history
                (
                    id int primary key auto_increment,
                    notice varchar(1000) comment '通知信息',
                    level varchar(10) comment '通知等级',
                    createtime varchar(20) comment '创建时间'
                )
                """;
        stmt.executeUpdate(createGitProjectNoticeHistoryTable);

        // 初始化仓库权限表
        String createGitAuthenticationTable = """
                create table git_auth_info
                (
                    id int primary key auto_increment,
                    type varchar(10) comment '仓库类型',
                    username varchar(50) comment '用户名',
                    password varchar(50) comment '密码',
                    createtime varchar(20) comment '创建时间'
                )
                """;
        stmt.executeUpdate(createGitAuthenticationTable);
        LOGGER.info("h2 仓库权限表创建成功");
    }

    public static GitAuthInfo queryAuthInfo(String type) {
        try (Connection conn = getConnection()) {
            String sql = "select * from git_auth_info where type = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, type);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return new GitAuthInfo(
                        resultSet.getInt("id"),
                        resultSet.getString("type"),
                        resultSet.getString("username"),
                        resultSet.getString("password"),
                        resultSet.getString("createtime")
                );
            }
        } catch (SQLException e) {
            LOGGER.error("通过 {} 查询权限信息异常：{}", type, e.getMessage());
        }
        return null;
    }

    public static List<GitAuthInfo> queryAllAuthInfo() {
        List<GitAuthInfo> list = new ArrayList<>();
        try (Connection conn = getConnection()) {
            String sql = "select * from git_auth_info";
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new GitAuthInfo(
                        resultSet.getInt("id"),
                        resultSet.getString("type"),
                        resultSet.getString("username"),
                        resultSet.getString("password"),
                        resultSet.getString("createtime")
                ));
            }
        } catch (SQLException e) {
            LOGGER.error("获取所有权限信息异常：{}", e.getMessage());
        }
        return list;
    }

    public static int insertAuthInfo(GitAuthInfo authInfo) throws H2Exception {
        try (Connection conn = getConnection()) {
            String sql = "INSERT INTO git_auth_info(type, username, password, createtime) values(?,?,?,?)";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, authInfo.type());
            statement.setString(2, authInfo.username());
            statement.setString(3, authInfo.password());
            statement.setString(4, DateUtils.formatDateTime(new Date()));
            return statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("记录权限信息异常：{}", e.getMessage());
            throw new H2Exception("记录权限信息异常");
        }
    }

    public static int updateAuthInfo(String type, String username, String password) throws H2Exception {
        try (Connection conn = getConnection()) {
            String sql = "update git_auth_info set username = ?, password = ? where type = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, username);
            statement.setString(2, password);
            statement.setString(3, type);
            return statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("通过 {} 更新权限信息为 {} 失败：{}", type, username, e.getMessage());
            throw new H2Exception("更新权限信息异常");
        }
    }

    public static int deleteAuthInfo(String type) {
        try (Connection conn = getConnection()) {
            String sql = "delete from git_auth_info where type = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, type);
            return statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("通过 {} 删除权限信息失败：{}", type, e.getMessage());
        }
        return 0;
    }

    /**
     * 初始化字典表数据
     *
     * @throws SQLException
     */
    public static void initGitProjectDictData() throws SQLException {
        Date currentDate = new Date();
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        String dateTime = DateUtils.formatDateTime(currentDate);
        String sql = "insert into git_project_dict(label, label_value, description, createtime, updatetime) values('GIT_DB_INIT','1','项目数据库初始化标识：0 未初始化，1 初始化', " +
                "'" + dateTime + "', '" + dateTime + "')";
        stmt.addBatch(sql);
        String sql2 = "insert into git_project_dict(label, label_value, description, createtime, updatetime) values('GIT_CURRENT_THEME','Primer Light','项目当前主题', " +
                "'" + dateTime + "', '" + dateTime + "')";
        stmt.addBatch(sql2);
        String sql3 = "insert into git_project_dict(label, label_value, description, createtime, updatetime) values('GIT_CURRENT_CLONE_DIR','','项目克隆路径', " +
                "'" + dateTime + "', '" + dateTime + "')";
        stmt.addBatch(sql3);
        String sql4 = "insert into git_project_dict(label, label_value, description, createtime, updatetime) values('GIT_CURRENT_LOCAL_DIR','','本地项目路径', " +
                "'" + dateTime + "', '" + dateTime + "')";
        stmt.addBatch(sql4);
        stmt.executeBatch();
        LOGGER.info("h2 字典表数据初始化成功");
    }

    public static String queryDictByLabel(String label, String defaultValue) {
        String value = defaultValue;
        try (Connection conn = getConnection()) {
            String sql = "select * from git_project_dict where label = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, label);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                value = resultSet.getString("label_value");
            }
        } catch (SQLException e) {
            LOGGER.error("通过 {} 查询数据异常：{}", label, e.getMessage());
        }
        LOGGER.info("通过 {} 查询数据结果：{}", label, value);
        return value;
    }

    public static void updateDictData(String label, String value) {
        try (Connection conn = getConnection()) {
            String sql = "update git_project_dict set label_value = ? where label = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, value);
            statement.setString(2, label);
            statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("通过 {} 更新数据为 {} 失败：{}", label, value, e.getMessage());
        }
        LOGGER.info("通过 {} 更新数据为 {} 成功", label, value);
    }

    public static void insertNoticeHistory(String notice, String level) {
        try (Connection conn = getConnection()) {
            String sql = "INSERT INTO git_project_notice_history(notice, level, createtime) values(?,?,?)";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, notice);
            statement.setString(2, level);
            statement.setString(3, DateUtils.formatDateTime(new Date()));
            statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("记录 {} 数据异常：{}", notice, e.getMessage());
        }
        LOGGER.info("记录 {} 数据成功", notice);
    }

    public static List<GitNotice> queryNoticeHistory(String level, boolean sort, int limit) {
        List<GitNotice> result = new ArrayList<>();
        try (Connection conn = getConnection()) {
            String sql = "select * from git_project_notice_history " + (StringUtils.isBlank(level) ? "" : "where level = ?") + " order by createtime " + (sort ? "" : "desc") + " limit ? ";
            PreparedStatement statement = conn.prepareStatement(sql);
            if (StringUtils.isBlank(level)) {
                statement.setInt(1, limit);
            } else {
                statement.setString(1, level);
                statement.setInt(2, limit);
            }
            statement.executeQuery();

            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                GitNotice gitNotice = new GitNotice(resultSet.getInt("id"),
                        resultSet.getString("label"), resultSet.getString("label_value"),
                        resultSet.getString("createtime"));
                result.add(gitNotice);
            }
        } catch (SQLException e) {
            LOGGER.error("通过 {} 获取通知数据异常：{}", level, e.getMessage());
        }
        return result;
    }

    public static List<GitNotice> queryNoticeHistory(boolean sort, int limit) {
        return queryNoticeHistory(null, sort, limit);
    }

    public static void insertProjectInfo(GitProject project) {
        try (Connection conn = getConnection()) {
            String sql = """
                    INSERT INTO git_project_info(name, author,
                    branch, remote, local, createtime, updatetime
                    ) values(?,?,?,?,?,?,?)
                    """;
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, project.name().get());
            statement.setString(2, project.author().get());
            statement.setString(3, project.branch().get());
            statement.setString(4, project.remote());
            statement.setString(5, project.local().get());
            statement.setString(6, project.createTime());
            statement.setString(7, project.updateTime().get());
            statement.executeUpdate();

            existsGitProjects(project);
        } catch (SQLException e) {
            LOGGER.error("项目信息保存异常：{}", e.getMessage());
        }
        LOGGER.info("项目信息 {} 保存成功", project);
    }

    public static void queryGitProjects() {
        try (Connection conn = getConnection()) {
            String sql = "select * from git_project_info order by id";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.executeQuery();

            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                GitProject gitProject = new GitProject(
                        new SimpleIntegerProperty(resultSet.getInt("id")),
                        new SimpleStringProperty(resultSet.getString("name")),
                        new SimpleStringProperty(resultSet.getString("author")),
                        new SimpleStringProperty(resultSet.getString("branch")),
                        resultSet.getString("createtime"),
                        new SimpleStringProperty(resultSet.getString("updatetime")),
                        resultSet.getString("remote"),
                        new SimpleStringProperty(resultSet.getString("local")),
                        new SimpleStringProperty(resultSet.getString("description")),
                        new SimpleStringProperty(resultSet.getString("remark")),
                        new SimpleIntegerProperty(resultSet.getInt("level")),
                        new SimpleDoubleProperty(0.0),
                        new SimpleBooleanProperty(false)
                );
                FxApplicationContextUtils.GIT_PROJECT_OBSERVABLE_LIST.add(gitProject);
            }
        } catch (SQLException e) {
            LOGGER.error("获取所有项目信息异常：{}", e.getMessage());
        }
    }

    public static boolean existsGitProjects(GitProject project) {
        try (Connection conn = getConnection()) {
            String sql = "select id from git_project_info where name = ? and author = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, project.name().get());
            statement.setString(2, project.author().get());
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                project.id().set(resultSet.getInt("id"));
                return true;
            }
        } catch (SQLException e) {
            LOGGER.error("根据 {}、{} 获取项目信息异常：{}", project.name().get(), project.author().get(), e.getMessage());
        }
        return false;
    }

    public static boolean existsGitProjects(String name, String author) {
        try (Connection conn = getConnection()) {
            String sql = "select id from git_project_info where name = ? and author = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, author);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return true;
            }
        } catch (SQLException e) {
            LOGGER.error("根据 {}、{} 获取项目信息异常：{}", name, author, e.getMessage());
        }
        return false;
    }

    public static void updateGitProject(GitProject project) {
        project.updateTime().set(DateUtils.formatDateTime(new Date()));
        try (Connection conn = getConnection()) {
            String sql = "update git_project_info set branch = ? ,updatetime = ?, local = ?, description = ?, remark = ?, level = ? where id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, project.branch().get());
            statement.setString(2, project.updateTime().get());
            statement.setString(3, project.local().get());
            statement.setString(4, project.description().get());
            statement.setString(5, project.remark().get());
            statement.setInt(6, project.level().get());
            statement.setInt(7, project.id().get());
            statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("更新项目信息异常：{}", e.getMessage());
        }
    }

    public static int deleteGitProject(GitProject project) throws H2Exception {
        try (Connection conn = getConnection()) {
            String sql = "delete from git_project_info where id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, String.valueOf(project.id().get()));
            return statement.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error("删除项目信息异常：{}", e.getMessage());
            throw new H2Exception();
        }
    }
}
